/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Examen;
import model.Cour;
import model.Personne;

/**
 *
 * @author Paul
 */
public class DAOExamen  {

    public Examen find(int id) {
        String query = "SELECT * FROM `examen` WHERE `id_examen`= "+id+"";
        Examen examen = null;
        examen = findGeneral(query).get(0);
        return examen;
    }

    public void create(String nomExam) {
        String query="INSERT INTO `examen`(`nom`) VALUES (?)";
        int nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement  ps=null;
        
         /**lancement de la connexion à la bd et de la requete **/
        try {
            Connection conn = DBConnection.getConnection();//Creation de la connexion
            ps= conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            
        /** remplacement des points d'interogation dans la query par les variables**/  
            ps.setString(1,nomExam);
            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat
            
            if(rs.next()){//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean update(Examen obj) {
        String query="UPDATE `examen` SET `id_examen`= ?,`nom`= ? WHERE `id_examen` = ?";
        int nb = 0;
        PreparedStatement  ps=null;
        boolean del = false;
         try {
            Connection conn = DBConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
           ps.setInt(1, obj.getId());
           ps.setString(2,obj.getNomExamen());
           ps.setInt(3, obj.getId());
           System.out.println(obj.getId());
           nb = ps.executeUpdate();//lancement de la requête
            if (nb >= 1) {
                del = true;
            }
         } catch (SQLException e) {
            e.printStackTrace();
        }
         return del;
    }

    public boolean delete(Examen obj) {
        boolean del = false;
        String query="DELETE FROM `examen` WHERE `id_examen` = ?";
        Integer nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement ps = null;
        try {
            Connection conn = DBConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
           ps.setInt(1, obj.getId());
           System.out.println(obj.getId());
            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat
            if (rs.next()) {//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
                System.out.println(nb);
            }
            if (nb >= 1) {
                del = true;
            }
         } catch (SQLException e) {
            e.printStackTrace();
        }
        return del;
        
    }

    public ArrayList<Examen> getAll() {
        String query ="SELECT e.id_examen,e.nom, c.nom FROM examen e, cour c, cour_exam_asso cea where cea.id_examen=e.id_examen and cea.id_cour = c.id_cour";
        return findGeneral(query);
    }
    
    public ArrayList<Examen> getAllExamFromCour(int idCour) {
        String query ="SELECT DISTINCT id_examen FROM  cour_exam_asso  where id_cour = "+idCour+"";    
        PreparedStatement st = null;
        int id_exam = 0;
            try {
                Connection conn = DBConnection.getConnection();
                st = conn.prepareStatement(query);
                ResultSet res = st.executeQuery(query);
                while (res.next()) {
                    id_exam = res.getInt("id_examen");
                }
             } catch (SQLException e) {
            e.printStackTrace();
        }
         String query2 = "SELECT e.nom, e.id_examen FROM examen e WHERE e.id_examen = "+id_exam+"";
        return findGeneral(query2);
    }

    public ArrayList<Examen> getExamFromCour(int id_cour) {
        String query ="select e.id_examen,e.nom from examen e, cour_exam_asso cea where cea.id_examen=e.id_examen and cea.id_cour="+id_cour+";";
        return findGeneral(query);
    }


    
    private ArrayList<Examen> findGeneral(String requete) {
        ArrayList<Examen> examens = new ArrayList<Examen>();
        Statement st = null;
        Examen examen = null;
        try {
            Connection conn = DBConnection.getConnection();
            st = conn.createStatement();
            ResultSet res = st.executeQuery(requete);
            while (res.next()) {
                examen = createRes(res);
                examens.add(examen);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return examens;
    }

    private Examen createRes(ResultSet res) {
        Examen examen = null;
        try {
            int idE = res.getInt("id_examen");
            String nom = res.getString("nom");
            examen = new Examen(idE, nom);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return examen;
    }

  
}
